USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[TeamMember] スクリプト日付: 2014/05/07 13:03:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TeamMember]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TeamMember];
GO

----チームタブ（所属別、競技別）に
CREATE PROCEDURE [dbo].[TeamMember]
	@TAIKAINO int,
	@TEAMNO int
AS
SELECT 
	K.KYOGISYANO, 
	convert(varchar,K.NUMBER1) + convert(varchar,K.NUMBER2) AS NUMBER2, 
	K.KYOGISYAKANA + '<br>' + K.KYOGISYAMEI AS KYOGISYAMEI, 
	dbo.Seibetu(K.SEIBETU) AS SEIBETU, 
	S.SYOZOKUMEI 
FROM 
	TBL_KYOGISYA AS K, 
	TBL_TEAM AS T, 
	TBL_SYOZOKU AS S 
WHERE 
	K.TAIKAINO = @TAIKAINO 
	AND K.TAIKAINO = T.TAIKAINO 
	AND K.TAIKAINO = S.TAIKAINO 
	AND T.TEAMNO = @TEAMNO 
	AND T.KYOGISYANO = K.KYOGISYANO 
	AND K.SYOZOKUNO1 = S.SYOZOKUNO